﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using School.Pages.DAL;

namespace School.Entities
{
    public class Bus_Repository
    {
        SchoolEntities db = new SchoolEntities();

        public IQueryable<SRV_BUS_Student> GetBus_Student()
        {
            var students = from s in db.SRV_BUS_Student
                           where !(db.SRV_BUS_Student_Temp.Any(c => c.Student_Id == s.Student_Id && c.From <= DateTime.Now && c.To >= DateTime.Now))
                           select s;
            return students;
        }
        public IQueryable<SRV_BUS_Student_Temp> GetBus_StudentTemp()
        {
            var students = from s in db.SRV_BUS_Student_Temp
                           where s.From <= DateTime.Now && s.To >= DateTime.Now
                           select s;
            return students;
        }
        public IQueryable<ORG_Stage> Get_stage()
        {
            var ss = from s in db.ORG_Stage
                     where s.Id == 4
                     select s;
            return ss;
        }

        public IQueryable<Bus_StudentViewModel> GetbusBusGoSid(int Sid)
        {

            var student = from s in GetBus_Student()
                          where s.Student_Id == Sid
                          select new Bus_StudentViewModel
                          {
                              Id = s.Id,
                              Student_Id = s.TE_Student.RegistrationNumber,
                              studentFName = s.TE_Student.Ar_FirstName,
                              studentSName = s.TE_Student.Ar_SecondName,
                              studentTName = s.TE_Student.Ar_ThirdNmae,
                              studentFoName = s.TE_Student.Ar_FourthName,
                              Bus_Go_Id = s.Bus_Go_Id,
                              BusGoName = s.SRV_Bus.Name,
                              Station_Go_Id = s.Station_Go_Id,
                              StationGoName = s.SRV_Bus_Station.Name,
                              Passenger_number = s.SRV_Bus.Passenger_number,
                              Bus_Code = s.SRV_Bus.Bus_Code,
                              DistrictGo = s.SRV_Bus_Station.LU_District.Name,
                              statue = true
                          };

            var student_TEMP = from s in GetBus_StudentTemp()
                               where s.Student_Id == Sid
                               select new Bus_StudentViewModel
                               {
                                   Id = s.Id,
                                   Student_Id = s.TE_Student.RegistrationNumber,
                                   studentFName = s.TE_Student.Ar_FirstName,
                                   studentSName = s.TE_Student.Ar_SecondName,
                                   studentTName = s.TE_Student.Ar_ThirdNmae,
                                   studentFoName = s.TE_Student.Ar_FourthName,
                                   Bus_Go_Id = s.Bus_Go_Id,
                                   BusGoName = s.SRV_Bus.Name,
                                   Station_Go_Id = s.Station_Go_Id,
                                   StationGoName = s.SRV_Bus_Station.Name,
                                   Passenger_number = s.SRV_Bus.Passenger_number,
                                   Bus_Code = s.SRV_Bus.Bus_Code,
                                   DistrictGo = s.SRV_Bus_Station.LU_District.Name,
                                   statue = false
                               };

            if (student_TEMP.Count() != 0)
                return student_TEMP;

            else if (student.Count() != 0)
                return student;
            else
                return student_TEMP;
        }
        public IQueryable<Bus_StudentViewModel> GetbusBusBackSid(int Sid)
        {

            var student = from s in GetBus_Student()
                          where s.Student_Id == Sid
                          select new Bus_StudentViewModel
                          {
                              Id = s.Id,
                              Student_Id = s.TE_Student.RegistrationNumber,
                              studentFName = s.TE_Student.Ar_FirstName,
                              studentSName = s.TE_Student.Ar_SecondName,
                              studentTName = s.TE_Student.Ar_ThirdNmae,
                              studentFoName = s.TE_Student.Ar_FourthName,
                              Bus_Return_Id = s.Bus_Return_Id,
                              BusBackName = s.SRV_Bus1.Name,
                              Station_Return_Id = s.Station_Return_Id,
                              StationBackName = s.SRV_Bus_Station1.Name,
                              Passenger_number = s.SRV_Bus.Passenger_number,
                              Bus_Code = s.SRV_Bus.Bus_Code,
                              DistrictBack = s.SRV_Bus_Station1.LU_District.Name,
                              statue = true
                          };

            var student_TEMP = from s in GetBus_StudentTemp()
                               where s.Student_Id == Sid
                               select new Bus_StudentViewModel
                               {
                                   Id = s.Id,
                                   Student_Id = s.TE_Student.RegistrationNumber,
                                   studentFName = s.TE_Student.Ar_FirstName,
                                   studentSName = s.TE_Student.Ar_SecondName,
                                   studentTName = s.TE_Student.Ar_ThirdNmae,
                                   studentFoName = s.TE_Student.Ar_FourthName,
                                   Bus_Return_Id = s.Bus_Return_Id,
                                   BusBackName = s.SRV_Bus1.Name,
                                   Station_Return_Id = s.Station_Return_Id,
                                   StationBackName = s.SRV_Bus_Station1.Name,
                                   Passenger_number = s.SRV_Bus.Passenger_number,
                                   Bus_Code = s.SRV_Bus.Bus_Code,
                                   DistrictBack = s.SRV_Bus_Station1.LU_District.Name,
                                   statue = false
                               };

            if (student_TEMP.Count() != 0)
                return student_TEMP;

            else if (student.Count() != 0)
                return student;
            else
                return student_TEMP;
        }

        public IQueryable<BusViewModel> GetBusInfo(int bus_id)
        {
            var bus = from s in db.SRV_Bus
                      where s.Id == bus_id
                      select new BusViewModel
                      {
                          Id = s.Id,
                          Name = s.Name,
                          Bus_Code = s.Bus_Code,
                      };
            return bus;
        }
        public IQueryable<Bus_StudentViewModel> GetStudentByBus(int bus_id)
        {
            var student = from s in GetBus_Student()
                          where s.Bus_Go_Id == bus_id || s.Bus_Return_Id == bus_id
                          select new Bus_StudentViewModel
                          {
                              Id = s.Id,
                              Student_Id = s.TE_Student.RegistrationNumber,
                              studentFName = s.TE_Student.Ar_FirstName,
                              studentSName = s.TE_Student.Ar_SecondName,
                              studentTName = s.TE_Student.Ar_ThirdNmae,
                              studentFoName = s.TE_Student.Ar_FourthName,
                              Bus_Go_Id = s.Bus_Go_Id,
                              BusGoName = s.SRV_Bus.Name,
                              Bus_Return_Id = s.Bus_Return_Id,
                              BusBackName = s.SRV_Bus1.Name,
                              Station_Go_Id = s.Station_Go_Id,
                              Station_Return_Id = s.Station_Return_Id,
                              StationGoName = s.SRV_Bus_Station.Name,
                              StationBackName = s.SRV_Bus_Station1.Name,
                              Passenger_number = s.SRV_Bus.Passenger_number,
                              Bus_Code = s.SRV_Bus.Bus_Code,
                              DistrictGo = s.SRV_Bus_Station.LU_District.Name,
                              DistrictBack = s.SRV_Bus_Station1.LU_District.Name,
                              statue = true
                          };

            return student;

        }

        public IQueryable<Bus_StudentViewModel> GetStudentByBus_Temp(int bus_id)
        {
            var student = from s in GetBus_StudentTemp()
                          where s.Bus_Go_Id == bus_id || s.Bus_Return_Id == bus_id
                          select new Bus_StudentViewModel
                          {
                              Id = s.Id,
                              Student_Id = s.TE_Student.RegistrationNumber,
                              studentFName = s.TE_Student.Ar_FirstName,
                              studentSName = s.TE_Student.Ar_SecondName,
                              studentTName = s.TE_Student.Ar_ThirdNmae,
                              studentFoName = s.TE_Student.Ar_FourthName,
                              Bus_Go_Id = s.Bus_Go_Id,
                              BusGoName = s.SRV_Bus.Name,
                              Bus_Return_Id = s.Bus_Return_Id,
                              BusBackName = s.SRV_Bus1.Name,
                              Station_Go_Id = s.Station_Go_Id,
                              Station_Return_Id = s.Station_Return_Id,
                              StationGoName = s.SRV_Bus_Station.Name,
                              StationBackName = s.SRV_Bus_Station1.Name,
                              Passenger_number = s.SRV_Bus.Passenger_number,
                              Bus_Code = s.SRV_Bus.Bus_Code,
                              DistrictGo = s.SRV_Bus_Station.LU_District.Name,
                              DistrictBack = s.SRV_Bus_Station1.LU_District.Name,
                              statue = false
                          };

            return student;

        }

        public IQueryable<BusViewModel> GetBusByCamp(int? Camp_Id)
        {
            return (from s in db.SRV_Bus where s.Campus_Id == Camp_Id select new BusViewModel { Id = s.Id, Name = s.Name });

        }
        public IQueryable<SRV_Bus_Station_Price> Get_Bus_station()
        {
            var bus = from b in db.SRV_Bus_Station_Price select b;

            return bus;
        }
        public IQueryable<Bus_Dist_Station> Get_Bus_Dist_station(int? Camp_Id, int? Bus_id)
        {
            var bus = from s in Get_Bus_station()
                      select new Bus_Dist_Station { Bus_Id = s.Bus_Id, Bus_Name = s.SRV_Bus.Name, District_Id = s.District_Id, District_Name = s.LU_District.Name, Price_Bus_Id = s.Id, Station_Id = s.Station_Id, Station_Name = s.SRV_Bus_Station.Name, Campus_Id = s.Campus_Id, Bus_Code = s.SRV_Bus.Bus_Code, Price = s.Price };
            if (Camp_Id.HasValue && Camp_Id.Value > 0)
                bus = bus.Where(a => a.Campus_Id == Camp_Id);
            if (Bus_id.HasValue && Bus_id != null && Bus_id.Value > 0)
                bus = bus.Where(a => a.Bus_Id == Bus_id);
            return bus;

        }

        public IQueryable<Orderreport> Orderreport(int payment_id)
        {

            var pay = from p in db.SRV_Bus_Payment_Order
                      where p.Id == payment_id
                      select new Orderreport
                      {

                          order_Id = p.Id,
                          Camp_Name = p.TE_Student.ORG_Campus.Name,
                          certifi_Name = p.TE_Student.ORG_Certificate.Name,
                          Details = "إشتراك الباص",
                          Grad_Name = p.TE_Student.ORG_Grade.Name,
                          order_number = p.Payment_order_num,
                          stage_Name = p.TE_Student.ORG_Stage.Name,
                          student_FName = p.TE_Student.Ar_FirstName,
                          student_SName = p.TE_Student.Ar_SecondName,
                          student_TName = p.TE_Student.Ar_ThirdNmae,
                          student_FoName = p.TE_Student.Ar_FourthName,
                          Date = p.Date,
                          Total = p.Value

                      };
            return pay;

        }
        public IQueryable<Order_Discount> Order_Dis_report(int payment_id)
        {

            var pay = from p in db.SRV_BUS_PAYMENT_ORDER_Discounts
                      where p.Payment_order_Id == payment_id
                      select new Order_Discount
                      {
                          Order_Discount_id = p.Id,
                          discount_Name = p.ACC_Discount.Name,
                          code = p.ACC_Discount.Code,
                          Type = p.Type,
                          Value = p.value
                      };
            return pay;

        }
    }
}
